package ph.com.gs3.formalistics.model.dao;

import java.util.ArrayList;
import java.util.List;

import ph.com.gs3.formalistics.global.utilities.logging.FLLogger;
import ph.com.gs3.formalistics.model.tables.CommentsTable;
import ph.com.gs3.formalistics.model.tables.UserAccountTable;
import ph.com.gs3.formalistics.model.valueobjects.business.Comment;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;

public class CommentsDAO extends SQLiteDataAccessObject {

	public static final String TAG = CommentsDAO.class.getSimpleName();

	private String[] joinedTableColumns;
	private String[] columns;

	public CommentsDAO(Context context) {
		super(context);

		joinedTableColumns = CommentsTable.JOINED_TABLE_COLUMNS;
		columns = new CommentsTable().getTableColumns();

	}

	// ===================================================================================================
	// {{ Query Methods

	public String getDeletedCommentsLastUpdateDate(String server) {

		// TODO: change this later with a different last update date
		String whereClause = CommentsTable.COL_SERVER + "=?";
		String[] whereArgs = { server };

		Cursor cursor = database.query(CommentsTable.NAME, columns, whereClause, whereArgs, null,
		        null, CommentsTable.COL_DATE_CREATED + " DESC", "1");

		String lastUpdateDate = null;

		cursor.moveToFirst();
		if (!cursor.isAfterLast()) {
			int dateCreatedIndex = cursor.getColumnIndexOrThrow(CommentsTable.COL_DATE_CREATED);
			lastUpdateDate = cursor.getString(dateCreatedIndex);
		}
		cursor.close();

		return lastUpdateDate;

	}

	public List<Comment> getDocumentComments(String server, String formId, String documentWebId) {

		// @formatter:off
		String whereClause 	= CommentsTable.NAME + "." + CommentsTable.COL_SERVER 				+ "=? AND "
		        			+ CommentsTable.NAME + "." + CommentsTable.COL_DOCUMENT_FORM_WEB_ID + "=? AND "
		        			+ CommentsTable.NAME + "." + CommentsTable.COL_DOCUMENT_WEB_ID 		+ "=? AND "
		        			+ CommentsTable.NAME + "." + CommentsTable.COL_MARKED_FOR_DELETION	+ "=?";
		String[] whereArgs = {server, formId, documentWebId, "0"};
		// @formatter:on

		return queryComments(whereClause, whereArgs);

	}

	public List<Comment> getAllOutgoingComments(String server, String authorWebId) {

		// @formatter:off
		String whereClause 	= CommentsTable.NAME + "." + CommentsTable.COL_SERVER 			+ "=? AND "
		        			+ CommentsTable.NAME + "." + CommentsTable.COL_AUTHOR_WEB_ID 	+ "=? AND "
		        			+ CommentsTable.NAME + "." + CommentsTable.COL_IS_OUTGOING 		+ "=?";
		String[] whereArgs = {server, authorWebId, "1"};
		// @formatter:on

		return queryComments(whereClause, whereArgs);
	}

	public List<Comment> getAllCommentsMarkedForDeletion(String server, String authorWebId) {

		// @formatter:off
		String whereClause 	= CommentsTable.NAME + "." + CommentsTable.COL_SERVER 				+ "=? AND "
							+ CommentsTable.NAME + "." + CommentsTable.COL_AUTHOR_WEB_ID 		+ "=? AND "
				        	+ CommentsTable.NAME + "." + CommentsTable.COL_MARKED_FOR_DELETION 	+ "=?";
		String[] whereArgs = {server, authorWebId, "1"};
		// @formatter:on

		return queryComments(whereClause, whereArgs);

	}

	public boolean isCommentExisting(String server, String webId) {

		String whereClause = CommentsTable.COL_SERVER + "=? AND " + CommentsTable.COL_WEB_ID + "=?";
		String[] whereArgs = { server, webId };

		Cursor cursor = database.query(CommentsTable.NAME, new String[] { "1" }, whereClause,
		        whereArgs, null, null, null);
		cursor.moveToFirst();
		boolean isExisting = !cursor.isAfterLast();
		cursor.close();

		return isExisting;
	}

	public Comment getCommentByDbId(String dbId) {

		String whereClause = CommentsTable.NAME + "." + CommentsTable.COL_ID + "=?";
		String[] whereArgs = { dbId };

		return queryComment(whereClause, whereArgs);

	}

	public Comment getComment(String server, String webId) {

		String whereClause = CommentsTable.NAME + "." + CommentsTable.COL_SERVER + "=? AND "
		        + CommentsTable.NAME + "." + CommentsTable.COL_WEB_ID + "=?";
		String[] whereArgs = { server, webId };

		return queryComment(whereClause, whereArgs);
	}

	public Comment getCommentById(String dbId) {

		String whereClause = CommentsTable.NAME + "." + CommentsTable.COL_ID + "=?";
		String[] whereArgs = { dbId };

		return queryComment(whereClause, whereArgs);

	}

	public Comment queryComment(String whereClause, String[] whereArgs) {
		// @formatter:off
		String selectColumns = generateSelectClauseFromArray(joinedTableColumns);
		String query 	= "SELECT " + selectColumns + " FROM " + CommentsTable.NAME
						+ " LEFT JOIN " + UserAccountTable.NAME
						+ " ON " 	+ CommentsTable.COL_AUTHOR_WEB_ID 	
									+ " = " + UserAccountTable.NAME + "." + UserAccountTable.COL_WEB_ID
						+ " AND " 	+ CommentsTable.NAME + "." + CommentsTable.COL_SERVER		
									+ " = " + UserAccountTable.NAME + "." + UserAccountTable.COL_SERVER
						+ " WHERE " + whereClause;
		// @formatter:on

		Cursor cursor = database.rawQuery(query, whereArgs);

		cursor.moveToFirst();

		Comment comment = null;

		if (!cursor.isAfterLast()) {
			comment = cursorToComment(cursor);
		}

		cursor.close();

		return comment;
	}

	public List<Comment> queryComments(String whereClause, String[] whereArgs) {
		return queryComments(whereClause, whereArgs, "ASC");
	}

	public List<Comment> queryComments(String whereClause, String[] whereArgs, String order) {

		// @formatter:off
		String selectColumns 	= generateSelectClauseFromArray(joinedTableColumns);
		String orderByClause 	= " ORDER BY " + CommentsTable.NAME + "." + CommentsTable.COL_DATE_CREATED 
								+ " " + order;
		
		String query 	= "SELECT " + selectColumns + " FROM " + CommentsTable.NAME
						+ " LEFT JOIN " + UserAccountTable.NAME
						+ " ON " 	+ CommentsTable.COL_AUTHOR_WEB_ID 	
									+ " = " + UserAccountTable.NAME + "." + UserAccountTable.COL_WEB_ID
						+ " AND " 	+ CommentsTable.NAME + "." + CommentsTable.COL_SERVER		
									+ " = " + UserAccountTable.NAME + "." + UserAccountTable.COL_SERVER
						+ " WHERE " + whereClause + orderByClause;
		// @formatter:on

		// FLLogger.d(TAG, "Get comments query: " + query);

		Cursor cursor = database.rawQuery(query, whereArgs);
		cursor.moveToFirst();

		List<Comment> comments = new ArrayList<>();

		while (!cursor.isAfterLast()) {
			comments.add(cursorToComment(cursor));
			cursor.moveToNext();
		}

		cursor.close();

		return comments;

	}

	// }}

	// ===================================================================================================
	// {{ Insert & Update Methods

	public Comment saveComment(Comment comment) {

		ContentValues cv = createCVFromComment(comment);

		int insertId = (int) database.insertOrThrow(CommentsTable.NAME, null, cv);
		return getCommentByDbId(Integer.toString(insertId));

	}

	public Comment updateOutgoingDocument(String dbId, String resultingWebId, String dateCreated)
	        throws UpdateFailedException {

		ContentValues cv = new ContentValues();
		cv.put(CommentsTable.COL_WEB_ID, resultingWebId);
		cv.put(CommentsTable.COL_DATE_CREATED, dateCreated);
		cv.put(CommentsTable.COL_IS_OUTGOING, 0);

		String whereClause = CommentsTable.COL_ID + "=?";
		String[] whereArgs = { dbId };

		int affectedRows = database.update(CommentsTable.NAME, cv, whereClause, whereArgs);
		if (affectedRows <= 0) {
			throw new UpdateFailedException("Comment to update not found.");
		} else {
			return getCommentById(dbId);
		}

	}

	public Comment updateComment(String webId, String server, Comment comment)
	        throws UpdateFailedException {

		ContentValues cv = createCVFromComment(comment);

		// the web id and the server cannot be updated
		cv.remove(CommentsTable.COL_WEB_ID);
		cv.remove(CommentsTable.COL_SERVER);

		String whereClause = CommentsTable.COL_WEB_ID + "=? AND " + CommentsTable.COL_SERVER + "=?";
		String[] whereArgs = { webId, server };

		int affectedRows = database.update(CommentsTable.NAME, cv, whereClause, whereArgs);
		if (affectedRows <= 0) {
			throw new UpdateFailedException("Comment to update not found.");
		} else {
			return getComment(server, webId);
		}

	}

	public Comment markCommentForDeletion(String dbId) throws UpdateFailedException {

		ContentValues cv = new ContentValues();

		cv.put(CommentsTable.COL_MARKED_FOR_DELETION, "1");

		String whereClause = CommentsTable.COL_ID + "=?";
		String[] whereArgs = { dbId };

		int affectedRows = database.update(CommentsTable.NAME, cv, whereClause, whereArgs);
		if (affectedRows <= 0) {
			throw new UpdateFailedException("Comment to update not found.");
		} else {
			return getCommentById(dbId);
		}

	}

	public void deleteComment(String dbId) throws DeleteFailedException {

		String whereClause = CommentsTable.COL_ID + "=?";
		String[] whereArgs = { dbId };

		int affectedRows = database.delete(CommentsTable.NAME, whereClause, whereArgs);
		if (affectedRows <= 0) {
			throw new DeleteFailedException("No comments with id " + dbId + " found to be deleted");
		} else if (affectedRows > 1) {
			FLLogger.w(TAG,
			        "There are more than one comments deleted when deleting comment with id "
			                + dbId);
		}

	}

	public void deleteComment(String server, String documentWebId) throws DeleteFailedException {

		String whereClause = CommentsTable.COL_SERVER + "=? AND " + CommentsTable.COL_WEB_ID + "=?";
		String[] whereArgs = { server, documentWebId };

		int affectedRows = database.delete(CommentsTable.NAME, whereClause, whereArgs);
		if (affectedRows <= 0) {
			throw new DeleteFailedException("No comments with web id " + documentWebId
			        + " found to be deleted");
		} else if (affectedRows > 1) {
			FLLogger.w(TAG,
			        "There are more than one comments deleted when deleting comment with web id "
			                + documentWebId + " and server " + server);
		}

	}

	// }}

	// ===================================================================================================
	// {{ Parser Methods

	private Comment cursorToComment(Cursor cursor) {

		// @formatter:off
		int idIndex 				= cursor.getColumnIndexOrThrow(CommentsTable.COL_ID);
		int webIdIndex 				= cursor.getColumnIndexOrThrow(CommentsTable.COL_WEB_ID);
		
		int documentFormWebIdIndex 	= cursor.getColumnIndexOrThrow(CommentsTable.COL_DOCUMENT_FORM_WEB_ID);
		int documentWebIdIndex 		= cursor.getColumnIndexOrThrow(CommentsTable.COL_DOCUMENT_WEB_ID);
		int serverIndex 			= cursor.getColumnIndexOrThrow(CommentsTable.COL_SERVER);

		int commentTextIndex 		= cursor.getColumnIndexOrThrow(CommentsTable.COL_COMMENT_TEXT);
		int dateCreatedIndex 		= cursor.getColumnIndexOrThrow(CommentsTable.COL_DATE_CREATED);
		int isOutgoingIndex			= cursor.getColumnIndexOrThrow(CommentsTable.COL_IS_OUTGOING);
		int markedForDeletionIndex	= cursor.getColumnIndexOrThrow(CommentsTable.COL_MARKED_FOR_DELETION);
		
		int authorWebIdIndex 		= cursor.getColumnIndexOrThrow(CommentsTable.COL_AUTHOR_WEB_ID);
		int authorDisplayNameIndex 	= cursor.getColumnIndexOrThrow(UserAccountTable.COL_DISPLAY_NAME);
		int authorImageURLIndex 	= cursor.getColumnIndexOrThrow(UserAccountTable.COL_IMAGE_URL);		
		
		String id 					= cursor.getString(idIndex);
		String webId 				= cursor.getString(webIdIndex);
		
		String documentFormWebId	= cursor.getString(documentFormWebIdIndex);
		String documentWebId 		= cursor.getString(documentWebIdIndex);
		String server 				= cursor.getString(serverIndex);
		
		String commentText 			= cursor.getString(commentTextIndex);
		String dateCreated 			= cursor.getString(dateCreatedIndex);
		boolean isOutgoing			= cursor.getInt(isOutgoingIndex) == 1;
		boolean markedForDeletion	= cursor.getInt(markedForDeletionIndex) == 1;
		
		String authorWebId 			= cursor.getString(authorWebIdIndex);
		String authorDisplayName 	= cursor.getString(authorDisplayNameIndex);
		String authorImageURL 		= cursor.getString(authorImageURLIndex);
		
		// @formatter:on

		Comment comment = new Comment();

		comment.setDbId(id);
		comment.setWebId(webId);

		comment.setDocumentFormWebId(documentFormWebId);
		comment.setDocumentWebId(documentWebId);
		comment.setServer(server);

		comment.setText(commentText);
		comment.setDateCreated(dateCreated);
		comment.setOutgoing(isOutgoing);
		comment.setMarkedForDeletion(markedForDeletion);

		comment.setAuthorWebId(authorWebId);
		comment.setAuthorDisplayName(authorDisplayName);
		comment.setAuthorImageURL(authorImageURL);

		return comment;

	}

	private ContentValues createCVFromComment(Comment comment) {

		ContentValues cv = new ContentValues();

		cv.put(CommentsTable.COL_WEB_ID, comment.getWebId());

		cv.put(CommentsTable.COL_DOCUMENT_FORM_WEB_ID, comment.getDocumentFormWebId());
		cv.put(CommentsTable.COL_DOCUMENT_WEB_ID, comment.getDocumentWebId());
		cv.put(CommentsTable.COL_SERVER, comment.getServer());

		cv.put(CommentsTable.COL_COMMENT_TEXT, comment.getText());
		cv.put(CommentsTable.COL_DATE_CREATED, comment.getDateCreated());

		cv.put(CommentsTable.COL_AUTHOR_WEB_ID, comment.getAuthorWebId());
		cv.put(CommentsTable.COL_IS_OUTGOING, comment.isOutgoing() ? 1 : 0);
		cv.put(CommentsTable.COL_MARKED_FOR_DELETION, comment.isMarkedForDeletion() ? 1 : 0);

		return cv;

	}

	// }}

}
